package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.IUserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements IUserDao {
    @Override
    public int addUser(UserModel userModel) {
        String sql = "insert into usr" +
                " (username,password,phone,birth,sex,hobby,intro,create_time)" +
                " values('" + userModel.getUsername() + "','" + userModel.getPassword() + "','" +
                userModel.getPhone() + "','" + userModel.getBirth() + "','" + userModel.getSex() + "','" +
                userModel.getHobby() + "','" + userModel.getIntro() +"','" +
                userModel.getCreateTime()+"')";
        return JDBCUtils.excute(sql);
    }

    @Override
    public int updateUser(UserModel userModel) {
        String sql = "update usr set username='" + userModel.getUsername() +
                "',password='" + userModel.getPassword() +
                "',phone='" + userModel.getPhone() +
                "',birth='" + userModel.getBirth() +
                "',sex='" + userModel.getSex() +
                "',hobby='" + userModel.getHobby() +
                "',intro='" + userModel.getIntro() +
                "',update_time='" + userModel.getUpdateTime() +
                "' where id=" + userModel.getId();
        return JDBCUtils.excute(sql);
    }

    @Override
    public int deleteUser(int id) {
        String sql = "delete from usr where id=" + id;
        return JDBCUtils.excute(sql);
    }

    @Override
    public UserModel getUser(int id) {
        String sql = "select * from usr where id=" + id;
        ResultSet rs = JDBCUtils.getResultSet(sql);
        UserModel userModel = null;
        try {
            while (rs.next()) {
                userModel = new UserModel();
                userModel.setId(rs.getInt("id"));
                userModel.setUsername(rs.getString("username"));
                userModel.setPassword(rs.getString("password"));
                userModel.setPhone(rs.getString("phone"));
                userModel.setBirth(rs.getString("birth"));
                userModel.setHobby(rs.getString("hobby"));
                userModel.setIntro(rs.getString("intro"));
                userModel.setSex(rs.getString("sex"));
                userModel.setCreateTime(rs.getString("create_time"));
                userModel.setUpdateTime(rs.getString("update_time"));
                userModel.setEnable(rs.getInt("enable"));
                userModel.setNote(rs.getString("note"));
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public List<UserModel> getUserList() {
        String sql = "select * from usr";
        ResultSet rs = JDBCUtils.getResultSet(sql);
        List<UserModel> userList = new ArrayList<>();
        UserModel userModel = null;
        try {
            while (rs.next()) {
                userModel = new UserModel();
                userModel.setId(rs.getInt("id"));
                userModel.setUsername(rs.getString("username"));
                userModel.setPassword(rs.getString("password"));
                userModel.setPhone(rs.getString("phone"));
                userModel.setBirth(rs.getString("birth"));
                userModel.setHobby(rs.getString("hobby"));
                userModel.setIntro(rs.getString("intro"));
                userModel.setSex(rs.getString("sex"));
                userModel.setCreateTime(rs.getString("create_time"));
                userModel.setUpdateTime(rs.getString("update_time"));
                userModel.setEnable(rs.getInt("enable"));
                userModel.setNote(rs.getString("note"));
                userList.add(userModel);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public List<UserModel> getUserList(int pageNum,int pageSize) {
        String sql = "select * from usr limit "+ (pageNum-1)*pageSize +","+pageSize;
        ResultSet rs = JDBCUtils.getResultSet(sql);
        List<UserModel> userList = new ArrayList<>();
        UserModel userModel = null;
        try {
            while (rs.next()) {
                userModel = new UserModel();
                userModel.setId(rs.getInt("id"));
                userModel.setUsername(rs.getString("username"));
                userModel.setPassword(rs.getString("password"));
                userModel.setPhone(rs.getString("phone"));
                userModel.setBirth(rs.getString("birth"));
                userModel.setHobby(rs.getString("hobby"));
                userModel.setIntro(rs.getString("intro"));
                userModel.setSex(rs.getString("sex"));
                userModel.setCreateTime(rs.getString("create_time"));
                userModel.setUpdateTime(rs.getString("update_time"));
                userModel.setEnable(rs.getInt("enable"));
                userModel.setNote(rs.getString("note"));
                userList.add(userModel);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public List<UserModel> getUserList(int pageNum, int pageSize, String sort) {
        String sql = "select * from usr order by "+ sort +" limit "+ (pageNum-1)*pageSize +","+pageSize;
        ResultSet rs = JDBCUtils.getResultSet(sql);
        List<UserModel> userList = new ArrayList<>();
        UserModel userModel = null;
        try {
            while (rs.next()) {
                userModel = new UserModel();
                userModel.setId(rs.getInt("id"));
                userModel.setUsername(rs.getString("username"));
                userModel.setPassword(rs.getString("password"));
                userModel.setPhone(rs.getString("phone"));
                userModel.setBirth(rs.getString("birth"));
                userModel.setHobby(rs.getString("hobby"));
                userModel.setIntro(rs.getString("intro"));
                userModel.setSex(rs.getString("sex"));
                userModel.setCreateTime(rs.getString("create_time"));
                userModel.setUpdateTime(rs.getString("update_time"));
                userModel.setEnable(rs.getInt("enable"));
                userModel.setNote(rs.getString("note"));
                userList.add(userModel);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public UserModel getUserByUsername(String username) {
        String sql = "select * from usr where username='" + username + "'";
        ResultSet rs = JDBCUtils.getResultSet(sql);
        UserModel userModel = null;//如果为空，则该用户名为未被占用
        try {
            if (rs.next()) {//如果不为空，则该用户名已被占用
                userModel = new UserModel();
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public List<UserModel> searchUser(String sql) {
        ResultSet rs = JDBCUtils.getResultSet(sql);
        List<UserModel> userList = new ArrayList<>();
        UserModel userModel = null;
        try {
            while (rs.next()) {
                userModel = new UserModel();
                userModel.setId(rs.getInt("id"));
                userModel.setUsername(rs.getString("username"));
                userModel.setPassword(rs.getString("password"));
                userModel.setPhone(rs.getString("phone"));
                userModel.setBirth(rs.getString("birth"));
                userModel.setHobby(rs.getString("hobby"));
                userModel.setIntro(rs.getString("intro"));
                userModel.setSex(rs.getString("sex"));
                userModel.setCreateTime(rs.getString("create_time"));
                userModel.setUpdateTime(rs.getString("update_time"));
                userModel.setEnable(rs.getInt("enable"));
                userModel.setNote(rs.getString("note"));
                userList.add(userModel);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public UserModel login(UserModel userModel) {
        String sql = "select * from usr where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet rs = JDBCUtils.getResultSet(sql);
        userModel = null;
        try {
            if (rs.next()) {
                userModel = new UserModel();
                userModel.setId(rs.getInt("id"));
                userModel.setUsername(rs.getString("username"));
                userModel.setPassword(rs.getString("password"));
                userModel.setPhone(rs.getString("phone"));
                userModel.setBirth(rs.getString("birth"));
                userModel.setHobby(rs.getString("hobby"));
                userModel.setIntro(rs.getString("intro"));
                userModel.setSex(rs.getString("sex"));
                userModel.setCreateTime(rs.getString("create_time"));
                userModel.setUpdateTime(rs.getString("update_time"));
                userModel.setEnable(rs.getInt("enable"));
                userModel.setNote(rs.getString("note"));
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public long getUserCount() {
        String sql = "select count(*) from usr";
        return JDBCUtils.getCount(sql);
    }

}
